/**
 * 
 */
package com.java.test.user.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.java.test.datacenter.dto.SumAccountInfo;
import com.java.test.user.dto.RunWaterInfo;
import com.java.test.util.PageInfo;

/**
 * @author Administrator
 *
 */
@Component
public class RunWaterMapper {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int selectrunwaterCount(String game_account, String startTime, String endTime) {
		String sql = "select count(*) from member_run_water where 1=1";

		if (game_account != null && !"".equals(game_account)) {
			sql += " and game_account='" + game_account + "'";
		}

		if ((startTime == null||"".equals(startTime)) && (endTime == null||"".equals(endTime))) {
			sql += " and  TO_DAYS(betting_time) = TO_DAYS(NOW())";
		} else {
			if ((startTime != null&&!"".equals(startTime)) && (endTime != null&&!"".equals(endTime))) {
				sql += " and betting_time>='" + startTime + "' and betting_time<='" + endTime + "'";
			} else {
				sql += " and  TO_DAYS(betting_time) = TO_DAYS('" + startTime + "')";
			}
			
		}

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? 0 : i;

	}

	public List<RunWaterInfo> selectrunWaterItemList(String game_account, PageInfo<RunWaterInfo> page, String startTime,
			String endTime) {

		RunWaterInfo infos = page.getInfo();

		String sql = "select * from member_run_water where 1=1";

		if (game_account != null && !"".equals(game_account)) {
			sql += " and game_account='" + game_account + "'";
		}

		if (infos.getGameType() != 0) {
			sql += " and game_type='" + infos.getGameType() + "'";
		}


		if ((startTime == null||"".equals(startTime)) && (endTime == null||"".equals(endTime))) {
			sql += " and  TO_DAYS(betting_time) = TO_DAYS(NOW())";
		} else {
			if ((startTime != null&&!"".equals(startTime)) && (endTime != null&&!"".equals(endTime))) {
				sql += " and betting_time>='" + startTime + "' and betting_time<='" + endTime + "'";
			} else {
				sql += " and  TO_DAYS(betting_time) = TO_DAYS('" + startTime + "')";
			}
			
		}

		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();

		sql += " limit ?,?";
		List<RunWaterInfo> list = jdbcTemplate.query(sql, new RunWaterInfo(), start, limit);
		return list;
	}

	public List<RunWaterInfo> selectrunWaterAllList(String game_account) {
		String sql = "select * from member_run_water where game_account='" + game_account + "'";
		List<RunWaterInfo> list = jdbcTemplate.query(sql, new RunWaterInfo());
		return list;
	}

	// 投注款
	public SumAccountInfo selectbetsData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from member_run_water where 1=1 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(betting_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null && endTime != null) {
				sqlitem += " and betting_time>='" + startTime + "' and betting_time<='" + endTime + "'";
			} else {
				sqlitem += " and  TO_DAYS(betting_time) = TO_DAYS('" + startTime + "')";
			}
		}
		int count = jdbcTemplate.queryForObject(sql + sqlitem, Integer.class);

		String sql2 = sqlitem + " group by game_account";
		List<Integer> list = jdbcTemplate.query(sql + sql2, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});

		String sqlamount = "select sum(bet_amount) from member_run_water where 1=1 ";
		Double totalAmount = jdbcTemplate.queryForObject(sqlamount + sqlitem, Double.class);

		SumAccountInfo info = new SumAccountInfo("", count,
				(list == null || list.size() <= 0) == true ? 0 : list.size(), totalAmount == null ? 0 : totalAmount);
		return info;
	}

	// 派彩款
	public SumAccountInfo selectPayoutData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from member_run_water where bet_result>0 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(betting_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null && endTime != null) {
				sqlitem += " and betting_time>='" + startTime + "' and betting_time<='" + endTime + "'";
			} else {
				sqlitem += " and  TO_DAYS(betting_time) = TO_DAYS('" + startTime + "')";
			}
		}
		int count = jdbcTemplate.queryForObject(sql + sqlitem, Integer.class);

		String sql2 = sqlitem + " group by game_account";
		List<Integer> list = jdbcTemplate.query(sql + sql2, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});

		String sqlamount = "select sum(bet_result) from member_run_water where bet_result>0 ";
		Double totalAmount = jdbcTemplate.queryForObject(sqlamount + sqlitem, Double.class);

		SumAccountInfo info = new SumAccountInfo("", count,
				(list == null || list.size() <= 0) == true ? 0 : list.size(), totalAmount == null ? 0 : totalAmount);
		return info;
	}

}
